<?php

require '../../FormWriter.php';
require '../../PhpTest.php';

/*
  Testing samples of implemented queries

  TODO: when PHP 5.3 will be out, nowdocs should be used instead of heredocs.
  They do not require escaping when embedding PHP code in the PhpTest::test 
  calls.
 */

$sea = new Sea();
echo '<pre>';
$pre = 'global $sea;
return $sea->convert_qbe(';
$post = ');';
PhpTest::verbose('body error passed');


# Selection queries:

PhpTest::test('qbe_convert-Sea.1', 'Print the names and ages of all sailors', <<<EOT
$pre
  array(
    'Sailors' => array(
      'sname' => 'P._N',
      'age'   => 'P._A'
    )
  )
$post
EOT
, 'SELECT DISTINCT T1.`sname`,T1.`age` FROM `Sailors` T1');

PhpTest::test('qbe_convert-Sea.2', 'Print all fields in relation Sailors', <<<EOT
$pre
  array(
    'Sailors' => array(
      '.rel' => 'P.'
    )
  )
$post
EOT
, 'SELECT DISTINCT T1.* FROM `Sailors` T1');

PhpTest::test('qbe_convert-Sea.3', 'Print all fields in relation Sailors where rating is 10', <<<EOT
$pre
  array(
    'Sailors' => array(
      '.rel'   => 'P.',
      'rating' => '10'
    )
  )
$post
EOT
, 'SELECT DISTINCT T1.* FROM `Sailors` T1 WHERE T1.`rating`=10');

PhpTest::test('qbe_convert-Sea.4', 'Select ages of sailors with rating less than 10, retaining duplicates', <<<EOT
$pre
  array(
    'Sailors' => array(
      '.rel'   => 'ALL.',
      'age'    => 'P.',
      'rating' => '<10'
    )
  )
$post
EOT
, 'SELECT T1.`age` FROM `Sailors` T1 WHERE T1.`rating`<10');

PhpTest::test('qbe_convert-Sea.5', 'Display the names, ages, and ratings of all sailors in ascending order by age, and for each age, in ascending order by rating', <<<EOT
$pre
  array(
    'Sailors' => array(
      'sname'  => 'P.',
      'rating' => 'P.AO(2)',
      'age'    => 'P.AO(1)'
    )
  )
$post
EOT
, 'SELECT DISTINCT T1.`sname`,T1.`rating`,T1.`age` FROM `Sailors` T1 ORDER BY T1.`age`,T1.`rating`');

PhpTest::test('qbe_convert-Sea.6', 'Find sailors with a reservation', <<<EOT
$pre
  array(
    'Sailors' => array(
      'sid'   => '_Id',
      'sname' => 'P._S'
    ),
    'Reserves' => array(
      'sid' => '_Id'
    )
  )
$post
EOT
, 'SELECT DISTINCT T1.`sname` FROM `Sailors` T1 JOIN `Reserves` T2 ON T1.`sid`=T2.`sid`');

PhpTest::test('qbe_convert-Sea.7', 'Find sailors who have reserved a boat for 8/24/96 and who are older than 25', <<<EOT
$pre
  array(
    'Sailors' => array(
      'sid'   => '_Id',
      'sname' => 'P._S',
      'age'   => '>25'
    ),
    'Reserves' => array(
      'sid' => '_Id',
      'day' => '1996-08-24'
    )
  )
$post
EOT
, "SELECT DISTINCT T1.`sname` FROM `Sailors` T1 JOIN `Reserves` T2 ON T1.`age`>25 AND T1.`sid`=T2.`sid` AND T2.`day`='1996-08-24'");

PhpTest::test('qbe_convert-Sea.8', 'Find the colors of Interlake boats reserved by sailors who have reserved a boat for 8/24/96 and who are older than 25', <<<EOT
$pre
  array(
    'Sailors' => array(
      'sid' => '_Id',
      'age' => '>25'
    ),
    'Reserves' => array(
      'sid' => '_Id',
      'bid' => '_B',
      'day' => '1996-08-24'
    ),
    'Boats' => array(
      'bid'   => '_B',
      'bname' => 'Interlake',
      'color' => 'P.'
    )
  )
$post
EOT
, "SELECT DISTINCT T3.`color` FROM `Sailors` T1 JOIN `Reserves` T2 ON T1.`age`>25 AND T1.`sid`=T2.`sid` AND T2.`day`='1996-08-24' JOIN `Boats` T3 ON T2.`bid`=T3.`bid` AND T3.`bname`='Interlake'");

PhpTest::test('qbe_convert-Sea.9', 'Prints the names of sailors who have reserved some boat that is also reserved by the sailor with id 22', <<<EOT
$pre
  array(
    'Sailors' => array(
      'sid'   => '_Id',
      'sname' => 'P._N'
    ),
    'Reserves' => array(
      'sid' => array('_Id', 22),
      'bid' => array('_B', '_B')
    ),
  )
$post
EOT
, 'SELECT DISTINCT T1.`sname` FROM `Sailors` T1 JOIN `Reserves` T2 ON T1.`sid`=T2.`sid` JOIN `Reserves` T3 ON T3.`sid`=22 AND T2.`bid`=T3.`bid`');

PhpTest::test('qbe_convert-Sea.10', 'Print the names of sailors who do not have a reservation', <<<EOT
$pre
  array(
    'Sailors' => array(
      'sid'   => '_Id',
      'sname' => 'P._S'
    ),
    'Reserves' => array(
      '.rel' => '!',
      'sid'  => '_Id'
    )
  )
$post
EOT
, 'SELECT DISTINCT T1.`sname` FROM `Sailors` T1 LEFT JOIN `Reserves` T2 ON T1.`sid`=T2.`sid` WHERE T2.`sid` IS NULL');

PhpTest::test('qbe_convert-Sea.11', 'Find sailors who are not (both) younger than 30 and rated higher than 4', <<<EOT
$pre
  array(
    'Sailors' => array(
      '.rel'   => array('', '!'),
      'sid'    => array('_Id', '_Id'),
      'sname'  => 'P._S',
      'rating' => array('', '>4'),
      'age'    => array('', '<30')
    )
  )
$post
EOT
, 'SELECT DISTINCT T1.`sname` FROM `Sailors` T1 LEFT JOIN `Sailors` T2 ON T1.`sid`=T2.`sid` AND T2.`rating`>4 AND T2.`age`<30 WHERE T2.`sid` IS NULL');

PhpTest::test('qbe_convert-Sea.12', 'Print the average age of sailors', <<<EOT
$pre
  # note we do not use external boxes
  array(
    'Sailors' => array(
      'age' => 'P.AVG._A'
    )
  )
$post
EOT
, 'SELECT DISTINCT AVG(T1.`age`) FROM `Sailors` T1');

PhpTest::test('qbe_convert-Sea.13', 'Print the average age of sailors counting each age once', <<<EOT
$pre
  array(
    'Sailors' => array(
      'age' => 'P.AVG.UNQ._A'
    )
  )
$post
EOT
, 'SELECT DISTINCT AVG(DISTINCT T1.`age`) FROM `Sailors` T1');

PhpTest::test('qbe_convert-Sea.14', 'Print average ages by rating', <<<EOT
$pre
  array(
    'Sailors' => array(
      'rating' => 'G.P.',
      'age'    => 'P.AVG._A'
    )
  )
$post
EOT
, 'SELECT DISTINCT T1.`rating`,AVG(T1.`age`) FROM `Sailors` T1 GROUP BY T1.`rating`');

PhpTest::test('qbe_convert-Sea.15', 'Print those ratings for which the average age is more than 30', <<<EOT
$pre
  array(
    'Sailors' => array(
      'rating' => 'G.P.',
      'age'    => '_A'
    ),
    '.cond'   => 'AVG._A > 30'
  )
$post
EOT
, 'SELECT DISTINCT T1.`rating` FROM `Sailors` T1 GROUP BY T1.`rating` HAVING AVG(T1.`age`) > 30');

PhpTest::test('qbe_convert-Sea.16', 'Print the sids of sailors who have reserved all boats for which there is some reservation', <<<EOT
$pre
  /*
    Note in our implementation COUNT does not eliminate duplicates
    by default.  UNQ should be used to count only the unique
    (distinct) values as required in this query.
	 */
  array(
    'Sailors' => array(
      'sid' => 'P.G._Id',
    ),
    'Reserves' => array(
      'sid' => '_Id',
      'bid' => array('_B1', '_B2')
    ),
    '.cond' => 'COUNT.UNQ._B1 = COUNT.UNQ._B2'
  )
$post
EOT
, 'SELECT DISTINCT T1.`sid` FROM `Sailors` T1 JOIN `Reserves` T2 ON T1.`sid`=T2.`sid` JOIN `Reserves` T3 GROUP BY T1.`sid` HAVING COUNT(DISTINCT T2.`bid`) = COUNT(DISTINCT T3.`bid`)');

PhpTest::test('qbe_convert-Sea.17', 'Incorrect version of query qbe_convert-Sea.16', <<<EOT
$pre
  array(
    'Sailors' => array(
      'sid'   => 'P.G._Id',
      'sname' => 'P.'
    ),
    'Reserves' => array(
      'sid' => '_Id',
      'bid' => array('_B1', '_B2')
    ),
    '.cond' => 'COUNT.UNQ._B1 = COUNT.UNQ._B2'
  )
$post
EOT
, 'SELECT DISTINCT T1.`sid`,T1.`sname` FROM `Sailors` T1 JOIN `Reserves` T2 ON T1.`sid`=T2.`sid` JOIN `Reserves` T3 GROUP BY T1.`sid` HAVING COUNT(DISTINCT T2.`bid`) = COUNT(DISTINCT T3.`bid`)');

PhpTest::test('qbe_convert-Sea.18', 'Legal version of qbe_convert-Sea.17. As qbe_convert-Sea.16 but aalso prints the names of sailors', <<<EOT
$pre
  array(
    'Sailors' => array(
      'sid'   => 'P.G._Id',
      'sname' => 'P.G'
    ),
    'Reserves' => array(
      'sid' => '_Id',
      'bid' => array('_B1', '_B2')
    ),
    '.cond' => 'COUNT.UNQ._B1 = COUNT.UNQ._B2'
  )
$post
EOT
, 'SELECT DISTINCT T1.`sid`,T1.`sname` FROM `Sailors` T1 JOIN `Reserves` T2 ON T1.`sid`=T2.`sid` JOIN `Reserves` T3 GROUP BY T1.`sid`,T1.`sname` HAVING COUNT(DISTINCT T2.`bid`) = COUNT(DISTINCT T3.`bid`)');

PhpTest::test('qbe_convert-Sea.19', 'Print the names of sailors who are younger than 20 or older than 30', <<<EOT
$pre
  array(
    'Sailors' => array(
      'sname' => 'P.',
      'age'   => '_A'
    ),
    '.cond' => '_A < 20 OR 30 < _A'
  )
$post
EOT
, 'SELECT DISTINCT T1.`sname` FROM `Sailors` T1 WHERE T1.`age` < 20 OR 30 < T1.`age`');

PhpTest::test('qbe_convert-Sea.20', 'Print the names of sailors who are either older than 20 or have a rating equal to 8', <<<EOT
$pre
  array(
    'Sailors' => array(
      'sname'  => 'P.',
      'age'    => '_A',
      'rating' => '_R'
    ),
    '.cond' => '20 < _A OR _R = 8'
  )
$post
EOT
, 'SELECT DISTINCT T1.`sname` FROM `Sailors` T1 WHERE 20 < T1.`age` OR T1.`rating` = 8');

PhpTest::test('qbe_convert-Sea.21', 'Print the names of sailors who are younger than 30 or older than 20 (explicit OR-query through a condition box)', <<<EOT
$pre
array(
  'Sailors' => array(
    'sname' => 'P.',
    'age'   => '_A'
  ),
  '.cond' => '_A<30 OR _A>20'
)
$post
EOT
, 'SELECT DISTINCT T1.`sname` FROM `Sailors` T1 WHERE T1.`age`<30 OR T1.`age`>20');

PhpTest::test('qbe_convert-Sea.22', 'Print the names of sailors who are both younger than 30 and older than 20 (explicit AND-query through a condition box)', <<<EOT
$pre
  array(
    'Sailors' => array(
      'sid'   => '_Id',
      'sname' => 'P.',
      'age'   => '_A'
    ),
    '.cond' => '_A<30 AND _A>20'
  )
$post
EOT
, 'SELECT DISTINCT T1.`sname` FROM `Sailors` T1 WHERE T1.`age`<30 AND T1.`age`>20');

PhpTest::test('qbe_convert-Sea.23', 'Print the name of each sailor along with the ratio rating/age', <<<EOT
$pre
  array(
    'Sailors' => array(
      'sname'  => 'P.',
      'rating' => '_R',
      'age'    => '_A',
      ''       => 'P._R/_A'
    )
  )
$post
EOT
, 'SELECT DISTINCT T1.`sname`,T1.`rating`/T1.`age` FROM `Sailors` T1');

PhpTest::test('qbe_convert-Sea.24', 'Same as qbe_convert-Sea.23 but uses an output table instead of an unnamed column', <<<EOT
$pre
  array(
    'Output'  => array(
      'rating/age' => 'P._R/_A'
    ),
    'Sailors' => array(
      'sname'  => 'P.',
      'rating' => '_R',
      'age'    => '_A'
    )
  )
$post
EOT
, 'SELECT DISTINCT T1.`sname`,T1.`rating`/T1.`age` AS `rating/age` FROM `Sailors` T1');

PhpTest::test('qbe_convert-Sea.25', 'Same as qbe_convert-Sea.24 but uses output columns (a FormWriter extension to allow unnamed column renaming)', <<<EOT
$pre
  array(
    'Sailors' => array(
      'sname'  => 'P.',
      'rating' => '_R',
      'age'    => '_A',
      'r/a'    => 'P._R/_A'
    )
  )
$post
EOT
, 'SELECT DISTINCT T1.`sname`,T1.`rating`/T1.`age` AS `r/a` FROM `Sailors` T1');

PhpTest::test('qbe_convert-Sea.26', 'Print the names of sailors along with the dates on which they have a boat reserved (using an unnamed column)', <<<EOT
$pre
  array(
    'Sailors' => array(
      'sid'   => '_Id',
      'sname' => 'P.',
      ''      => 'P._D'
    ),
    'Reserves' => array(
      'sid' => '_Id',
      'day' => '_D'
    )
  )
$post
EOT
, 'SELECT DISTINCT T1.`sname`,T2.`day` FROM `Sailors` T1 JOIN `Reserves` T2 ON T1.`sid`=T2.`sid`');

PhpTest::test('qbe_convert-Sea.27', 'Same as qbe_convert-Sea.26 but adds a condition to the dates', <<<EOT
$pre
  array(
    'Sailors' => array(
      'sid'   => '_Id',
      'sname' => 'P.',
      ''      => 'P._D'
    ),
    'Reserves' => array(
      'sid' => '_Id',
      'day' => '_D'
    ),
    '.cond' => "_D>'1996-08-09'"
  )
$post
EOT
, "SELECT DISTINCT T1.`sname`,T2.`day` FROM `Sailors` T1 JOIN `Reserves` T2 ON T1.`sid`=T2.`sid` WHERE T2.`day`>'1996-08-09'");


# Insertion queries

PhpTest::test('qbe_convert-Sea.28', 'Insert a new tuple into the Sailors relation', <<<EOT
$pre
  array(
    'Sailors' => array(
      '.rel'   => 'I.',
      'sid'    => 74,
      'sname'  => 'Janice',
      'rating' => 7,
      'age'    => 41
    )
  )
$post
EOT
, "INSERT INTO `Sailors` (`sid`,`sname`,`rating`,`age`) VALUES(74,'Janice',7,41)");

PhpTest::test('qbe_convert-Sea.29', 'Insert several tuples, computed essentially through a query, into the Sailors relation', <<<EOT
$pre
  array(
    'Sailors' => array(
      '.rel'   => 'I.',
      'sid'    => '_Id',
      'sname'  => '_N',
      # The rating field of every inserted tuple contains a null value.
      'age'    => '_A'
    ),
    'Students' => array(
      'sid'  => '_Id',
      'name' => '_N',
      'age'  => '_A',
    ),
    '.cond' => "_A > 18 OR _N LIKE 'C%'"
  )
$post
EOT
, "INSERT INTO `Sailors` (`sid`,`sname`,`age`) SELECT DISTINCT T1.`sid`,T1.`name`,T1.`age` FROM `Students` T1 WHERE T1.`age` > 18 OR T1.`name` LIKE 'C%'");

# Deletion queries

PhpTest::test('qbe_convert-Sea.30', 'Empty the Sailors relation', <<<EOT
$pre
	array(
		'Sailors' => array(
			'.rel'  => 'D.'
		)
	)
$post
EOT
, 'TRUNCATE TABLE `Sailors`');

PhpTest::test('qbe_convert-Sea.31', 'Delete all tuples with rating > 5 from the Sailors relation', <<<EOT
$pre
	array(
		'Sailors' => array(
			'.rel'   => 'D.',
			'rating' => '>5'
		)
	)
$post
EOT
, 'DELETE FROM T1 USING `Sailors` T1 WHERE T1.`rating`>5');

PhpTest::test('qbe_convert-Sea.32', 'Delete all reservations for sailors with rating < 4', <<<EOT
$pre
	array(
		'Sailors' => array(
			'sid'    => '_Id',
			'rating' => '<4'
		),
		'Reserves' => array(
			'.rel' => 'D.',
			'sid'  => '_Id'
		),
	)
$post
EOT
, 'DELETE FROM T2 USING `Sailors` T1 JOIN `Reserves` T2 ON T1.`rating`<4 AND T1.`sid`=T2.`sid`');

PhpTest::test('qbe_convert-Sea.33', 'Update the age of the sailor with sid 74 to be 42 years', <<<EOT
$pre
  array(
    'Sailors' => array(
      '.rel' => 'U.',
      'age'  => '42',
      'sid'  => 74,
    )
  )
$post
EOT
, 'UPDATE `Sailors` T1 SET T1.`age`=42 WHERE T1.`sid`=74');

PhpTest::test('qbe_convert-Sea.34', 'Change the age of sailor 74 from 41 to 42 by incrementing the age value', <<<EOT
$pre
  array(
    'Sailors' => array(
      '.rel' => 'U.',
      # Note that parenthesis around the expression are not needed.
      'age' => array('_A+1', '_A'),
      # Please note the equi-join condition on the key field is needed.
      'sid' => array(74, 74)
    )
  )
$post
EOT
/*
  TODO: this query can be optimized by avoiding the self-join:
  UPDATE `Sailors` T1 SET T1.`age`=T1.`age`+1 WHERE T1.`sid`=74
 */
, 'UPDATE `Sailors` T1 JOIN `Sailors` T2 ON T1.`sid`=74 AND T2.`sid`=74 SET T1.`age`=T2.`age`+1');

echo '</pre>';

# End of file
# vim: set ts=2 sw=2 et:
?>
